﻿using System.CodeDom;
using System.Text;
using MDSAdministration;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace MDSAdministrationTest
{
    [TestClass]
    public class DeploymentBuilderTests
    {
        StringBuilder ExpectedCameraResult = new StringBuilder();
        StringBuilder ExpectedNullCameraResult = new StringBuilder();
        StringBuilder ExpectedNullCameraExtraResult = new StringBuilder();
        StringBuilder ExpectedCameraviaBuildMembersResult = new StringBuilder();
        StringBuilder ExpectedNullCameraExtraWidthChangeResult = new StringBuilder();
        public DeploymentBuilderTests()
        {
            ExpectedCameraResult.Append("DECLARE @p9 mdm.MemberAttributes\r\n");
            ExpectedCameraResult.Append("INSERT INTO @p9 VALUES(N'CAMERA45',N'Name',N'camera',NULL,NULL,NULL)\r\n");
            ExpectedCameraResult.Append("\r\n");
            ExpectedCameraResult.Append("-- get UserID for: BRIGHTSTAR\\oszymczak\r\n");
            ExpectedCameraResult.Append("DECLARE @UserID INT\r\n");
            ExpectedCameraResult.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = 'BRIGHTSTAR\\oszymczak'\r\n");
            ExpectedCameraResult.Append("\r\n");
            ExpectedCameraResult.Append("DECLARE @Version_ID INT\r\n");
            ExpectedCameraResult.Append("DECLARE @Entity_ID INT\r\n");
            ExpectedCameraResult.Append("SELECT @Entity_ID = e.ID, @Version_ID = mv.ID FROM mdm.tblEntity e\r\n");
            ExpectedCameraResult.Append("INNER JOIN mdm.tblModel m ON m.ID = e.Model_ID\r\n");
            ExpectedCameraResult.Append("INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID\r\n");
            ExpectedCameraResult.Append("WHERE e.Name = 'Camera'\r\n");
            ExpectedCameraResult.Append("AND m.Name = 'SandBox'\r\n");
            ExpectedCameraResult.Append("\r\n");
            ExpectedCameraResult.Append("EXEC mdm.udpEntityMembersUpdate @User_ID=@UserID,@Version_ID=@Version_ID,@Entity_ID=@Entity_ID,\r\n");
            ExpectedCameraResult.Append("@MemberType_ID=1,@LogFlag=1,\r\n");
            ExpectedCameraResult.Append("@DoInheritanceRuleCheck=1,\r\n");
            ExpectedCameraResult.Append("@ShouldReturnMembersWithErrorsAsXml=0,\r\n");
            ExpectedCameraResult.Append("@IgnorePriorValues=1,\r\n");
            ExpectedCameraResult.Append("@MemberAttributes=@p9\r\n");
            ExpectedCameraResult.Append("GO\r\n");

            ExpectedNullCameraResult.Append("DECLARE @p9 mdm.MemberAttributes\r\n");
            ExpectedNullCameraResult.Append("INSERT INTO @p9 VALUES(N'CAMERA45',N'Name',NULL,NULL,NULL,NULL)\r\n");
            ExpectedNullCameraResult.Append("\r\n");
            ExpectedNullCameraResult.Append("-- get UserID for: BRIGHTSTAR\\OSzymczak\r\n");
            ExpectedNullCameraResult.Append("DECLARE @UserID INT\r\n");
            ExpectedNullCameraResult.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = 'BRIGHTSTAR\\OSzymczak'\r\n");
            ExpectedNullCameraResult.Append("\r\n");
            ExpectedNullCameraResult.Append("DECLARE @Version_ID INT\r\n");
            ExpectedNullCameraResult.Append("DECLARE @Entity_ID INT\r\n");
            ExpectedNullCameraResult.Append("SELECT @Entity_ID = e.ID, @Version_ID = mv.ID FROM mdm.tblEntity e\r\n");
            ExpectedNullCameraResult.Append("INNER JOIN mdm.tblModel m ON m.ID = e.Model_ID\r\n");
            ExpectedNullCameraResult.Append("INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID\r\n");
            ExpectedNullCameraResult.Append("WHERE e.Name = 'Camera'\r\n");
            ExpectedNullCameraResult.Append("AND m.Name = 'SandBox'\r\n");
            ExpectedNullCameraResult.Append("\r\n");
            ExpectedNullCameraResult.Append("EXEC mdm.udpEntityMembersUpdate @User_ID=@UserID,@Version_ID=@Version_ID,@Entity_ID=@Entity_ID,\r\n");
            ExpectedNullCameraResult.Append("@MemberType_ID=1,@LogFlag=1,\r\n");
            ExpectedNullCameraResult.Append("@DoInheritanceRuleCheck=1,\r\n");
            ExpectedNullCameraResult.Append("@ShouldReturnMembersWithErrorsAsXml=0,\r\n");
            ExpectedNullCameraResult.Append("@IgnorePriorValues=1,\r\n");
            ExpectedNullCameraResult.Append("@MemberAttributes=@p9\r\n");
            ExpectedNullCameraResult.Append("GO\r\n");

            ExpectedNullCameraExtraResult.Append("DECLARE @p9 mdm.MemberAttributes\r\n");
            ExpectedNullCameraExtraResult.Append("INSERT INTO @p9 VALUES(N'CAMERA45',N'Extra',NULL,NULL,NULL,NULL)\r\n");
            ExpectedNullCameraExtraResult.Append("\r\n");
            ExpectedNullCameraExtraResult.Append("-- get UserID for: BRIGHTSTAR\\OSzymczak\r\n");
            ExpectedNullCameraExtraResult.Append("DECLARE @UserID INT\r\n");
            ExpectedNullCameraExtraResult.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = 'BRIGHTSTAR\\OSzymczak'\r\n");
            ExpectedNullCameraExtraResult.Append("\r\n");
            ExpectedNullCameraExtraResult.Append("DECLARE @Version_ID INT\r\n");
            ExpectedNullCameraExtraResult.Append("DECLARE @Entity_ID INT\r\n");
            ExpectedNullCameraExtraResult.Append("SELECT @Entity_ID = e.ID, @Version_ID = mv.ID FROM mdm.tblEntity e\r\n");
            ExpectedNullCameraExtraResult.Append("INNER JOIN mdm.tblModel m ON m.ID = e.Model_ID\r\n");
            ExpectedNullCameraExtraResult.Append("INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID\r\n");
            ExpectedNullCameraExtraResult.Append("WHERE e.Name = 'Camera'\r\n");
            ExpectedNullCameraExtraResult.Append("AND m.Name = 'SandBox'\r\n");
            ExpectedNullCameraExtraResult.Append("\r\n");
            ExpectedNullCameraExtraResult.Append("EXEC mdm.udpEntityMembersUpdate @User_ID=@UserID,@Version_ID=@Version_ID,@Entity_ID=@Entity_ID,\r\n");
            ExpectedNullCameraExtraResult.Append("@MemberType_ID=1,@LogFlag=1,\r\n");
            ExpectedNullCameraExtraResult.Append("@DoInheritanceRuleCheck=1,\r\n");
            ExpectedNullCameraExtraResult.Append("@ShouldReturnMembersWithErrorsAsXml=0,\r\n");
            ExpectedNullCameraExtraResult.Append("@IgnorePriorValues=1,\r\n");
            ExpectedNullCameraExtraResult.Append("@MemberAttributes=@p9\r\n");
            ExpectedNullCameraExtraResult.Append("GO\r\n");

            ExpectedNullCameraExtraWidthChangeResult.Append("-------------------------------------------------------------------------------\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("-- UPDATE Attribute: TestAttribute\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("-------------------------------------------------------------------------------\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("BEGIN TRANSACTION\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("BEGIN TRY\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("UPDATE mdm.tblAttribute SET MUID = '943CB73E-3A02-498C-A701-2540653C5CAA', SortOrder = 1, ChangeTrackingGroup = 1 WHERE MUID = '943CB73E-3A02-498C-A701-2540653C5CAA'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("-- get UserID for: BRIGHTSTAR\\oszymczak\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("DECLARE @UserID INT\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = 'BRIGHTSTAR\\oszymczak'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("-- get Input Mask Name\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("DECLARE @InputMaskName NVARCHAR(250)\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("SELECT @InputMaskName = ListOption FROM mdm.tblList WHERE ListCode = 'lstInputMask' AND Group_ID = 1 AND OptionID = 1\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("EXEC mdm.udpAttributeSaveByMUID\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@Model_MUID = NULL, @Model_Name = 'SandBox',\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@Entity_MUID = NULL, @Entity_Name = 'TestEntity',\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@MemberType_ID = 1,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@Attribute_MUID = '943CB73E-3A02-498C-A701-2540653C5CAA',\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@Name = N'TestAttribute',\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@AttributeType_ID = 1,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@DisplayWidth = 40,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@DomainEntity_MUID = '',\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@DomainEntity_Name = '',\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@DataType_ID = 1,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@DataTypeInformation = 40,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@InputMask_Name = @InputMaskName,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@ChangeTrackingGroup = 1,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@SortOrder = 1,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("@User_ID = @UserID\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("DECLARE @EntityID INT\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("DECLARE @ModelID INT\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("DECLARE @StagingBase INT\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("DECLARE @EntityTable NVARCHAR(100)\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("DECLARE @TableColumn NVARCHAR(100)\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("DECLARE @Sqlquery NVARCHAR(MAX)\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("SELECT @EntityID = ID, @ModelID = Model_ID, @EntityTable = EntityTable FROM mdm.tblEntity WHERE MUID = '2343CB73E-3A02-498C-A701-2540653C5CBB'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("SELECT @TableColumn = a.TableColumn FROM mdm.tblEntity e INNER JOIN mdm.tblAttribute a ON a.Entity_ID = e.ID WHERE a.MUID = '943CB73E-3A02-498C-A701-2540653C5CAA'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("IF @EntityID IS NOT NULL\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("BEGIN\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("	ALTER TABLE stg.SandBox_Test_Leaf ALTER COLUMN TestAttribute NVARCHAR(40)\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("	SET @Sqlquery = 'ALTER TABLE mdm.' + @EntityTable + ' ALTER COLUMN ' + @TableColumn + ' NVARCHAR(40)'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("	EXEC sp_executesql @SqlQuery\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("	UPDATE mdm.tblAttribute SET DisplayWidth = 40, DataTypeInformation = 40 WHERE MUID = '943CB73E-3A02-498C-A701-2540653C5CAA'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("	UPDATE mdm.tblSubscriptionView SET IsDirty = 1 WHERE Entity_ID = @EntityID\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("	EXEC mdm.udpCreateViews @ModelID, @EntityID\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("END\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("ELSE\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("BEGIN\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("	PRINT 'Error Change size of attribute TestAttribute'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("	PRINT 'Error Entity name: TestEntity'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("	PRINT 'Error Entity has not been found or MUID is out of sync'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("END\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("COMMIT TRANSACTION\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("END TRY\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("BEGIN CATCH\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\tDECLARE\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\t@ErrorMessage NVARCHAR(4000),\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\t@ErrorSeverity INT,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\t@ErrorState INT;\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\tEXEC mdm.udpGetErrorInfo\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\t@ErrorMessage = @ErrorMessage OUTPUT,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\t@ErrorSeverity = @ErrorSeverity OUTPUT,\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\t@ErrorState = @ErrorState OUTPUT;\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\tROLLBACK TRANSACTION\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\tPRINT 'Error has occurred:'\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\tPRINT @ErrorMessage\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\tPRINT 'ErrorSeverity: ' + CAST(@ErrorSeverity AS NVARCHAR(30))\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("\tPRINT '@ErrorState: ' + CAST(@ErrorState AS NVARCHAR(30))\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("END CATCH\r\n");
            ExpectedNullCameraExtraWidthChangeResult.Append("GO\r\n");

            ExpectedCameraviaBuildMembersResult.Append("DECLARE @BatchTag NVARCHAR(50)\r\n");
            ExpectedCameraviaBuildMembersResult.Append("SELECT @BatchTag = REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')\r\n");
            ExpectedCameraviaBuildMembersResult.Append("PRINT 'BatchTag Details: ' + @BatchTag\r\n");
            ExpectedCameraviaBuildMembersResult.Append("INSERT INTO [stg].[Sandbox_Camera_Leaf]\r\n");
            ExpectedCameraviaBuildMembersResult.Append("(\r\n");
            ExpectedCameraviaBuildMembersResult.Append("	[ImportType]\r\n");
            ExpectedCameraviaBuildMembersResult.Append("	,[ImportStatus_ID]\r\n");
            ExpectedCameraviaBuildMembersResult.Append("	,[BatchTag]\r\n");
            ExpectedCameraviaBuildMembersResult.Append("	,[Code]\r\n");
            ExpectedCameraviaBuildMembersResult.Append("	,[Name]\r\n");
            ExpectedCameraviaBuildMembersResult.Append(")\r\n");
            ExpectedCameraviaBuildMembersResult.Append("SELECT 0 AS 'ImportType', 0 AS 'ImportStatus_ID', @BatchTag AS 'BatchTag', N'CAMERA45' AS 'Code', NULL AS 'Name'\r\n");
            ExpectedCameraviaBuildMembersResult.Append("\r\n");
            ExpectedCameraviaBuildMembersResult.Append("EXEC stg.udp_Sandbox_Camera_Leaf 'VERSION_1', 1, @BatchTag\r\n");
            ExpectedCameraviaBuildMembersResult.Append("GO\r\n");
            ExpectedCameraviaBuildMembersResult.Append(ExpectedCameraResult.ToString());

        }
        [TestMethod]
        public void specialCaseDataMemberBuilderCaseDifferentTest()
        {
            DeploymentBuilder db = new DeploymentBuilder();
            db.CurrentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
            StringBuilder sb = new StringBuilder();

            Common.LoadServer();
            db.Server = Common.ServerB;

            var aem = new ActionEntityMember();
            aem.EntityStagingTableName = "Sandbox_Camera";
            aem.EntityName = "Camera";

            var ca = new CompareAttribute() { AttributeTypeID = 1, DataTypeID = 1, DataTypeInformation = 100 };

            ActionMember am = new ActionMember();
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Name", ValueSource = "camera", ValueDestination = "CAMERA", Attribute = ca });
            aem.LActionMembers.Add(am);

            db.buildSpecialMembers(sb, aem);

            Assert.AreEqual(sb.ToString().ToLower(), ExpectedCameraResult.ToString().ToLower());
        }

        [TestMethod]
        public void specialCaseDataMemberBuilderCaseDifferent2Test()
        {
            DeploymentBuilder db = new DeploymentBuilder();
            db.CurrentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

            Common.LoadServer();
            db.Server = Common.ServerB;

            StringBuilder sb = new StringBuilder();

            var aem = new ActionEntityMember();
            aem.EntityStagingTableName = "Sandbox_Camera";
            aem.EntityName = "Camera";

            var ca = new CompareAttribute() {AttributeTypeID = 1, DataTypeID = 1, DataTypeInformation = 100};
            ActionMember am = new ActionMember();
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Code", ValueSource = "CAMERA45" });
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Name", ValueSource = "camera", ValueDestination = "CAMERA", Attribute = ca});
            
            aem.LActionMembers.Add(am);
            db.LActionEntityMembers.Add(aem);
            db.buildMembers(sb);

            Assert.AreEqual(sb.ToString().ToLower(), ExpectedCameraviaBuildMembersResult.ToString().ToLower());
        }

        [TestMethod]
        public void DataMemberBuilderCaseDifferentTest()
        {
            StringBuilder Expected = new StringBuilder();
            Expected.Append("DECLARE @BatchTag NVARCHAR(50)\r\n");
            Expected.Append("SELECT @BatchTag = REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')\r\n");
            Expected.Append("PRINT 'BatchTag Details: ' + @BatchTag\r\n");
            Expected.Append("INSERT INTO [stg].[Sandbox_Camera_Leaf]\r\n");
            Expected.Append("(\r\n");
            Expected.Append("	[ImportType]\r\n");
            Expected.Append("	,[ImportStatus_ID]\r\n");
            Expected.Append("	,[BatchTag]\r\n");
            Expected.Append("	,[Code]\r\n");
            Expected.Append("	,[Name]\r\n");
            Expected.Append(")\r\n");
            Expected.Append("SELECT 0 AS 'ImportType', 0 AS 'ImportStatus_ID', @BatchTag AS 'BatchTag', N'CAMERA45' AS 'Code', NULL AS 'Name'\r\n");
            Expected.Append("\r\n");
            Expected.Append("EXEC stg.udp_Sandbox_Camera_Leaf 'VERSION_1', 1, @BatchTag\r\n");
            Expected.Append("GO\r\n");
            Expected.Append("DECLARE @p9 mdm.MemberAttributes\r\n");
            Expected.Append("INSERT INTO @p9 VALUES(N'CAMERA45',N'Name',NULL,NULL,NULL,NULL)\r\n");
            Expected.Append("\r\n");
            Expected.Append("-- get UserID for: BRIGHTSTAR\\oszymczak\r\n");
            Expected.Append("DECLARE @UserID INT\r\n");
            Expected.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = 'BRIGHTSTAR\\oszymczak'\r\n");
            Expected.Append("\r\n");
            Expected.Append("DECLARE @Version_ID INT\r\n");
            Expected.Append("DECLARE @Entity_ID INT\r\n");
            Expected.Append("SELECT @Entity_ID = e.ID, @Version_ID = mv.ID FROM mdm.tblEntity e\r\n");
            Expected.Append("INNER JOIN mdm.tblModel m ON m.ID = e.Model_ID\r\n");
            Expected.Append("INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID\r\n");
            Expected.Append("WHERE e.Name = 'Camera'\r\n");
            Expected.Append("AND m.Name = 'SandBox'\r\n");
            Expected.Append("\r\n");
            Expected.Append("EXEC mdm.udpEntityMembersUpdate @User_ID=@UserID,@Version_ID=@Version_ID,@Entity_ID=@Entity_ID,\r\n");
            Expected.Append("@MemberType_ID=1,@LogFlag=1,\r\n");
            Expected.Append("@DoInheritanceRuleCheck=1,\r\n");
            Expected.Append("@ShouldReturnMembersWithErrorsAsXml=0,\r\n");
            Expected.Append("@IgnorePriorValues=1,\r\n");
            Expected.Append("@MemberAttributes=@p9\r\n");
            Expected.Append("GO\r\n");
            
            DeploymentBuilder db = new DeploymentBuilder();
            db.CurrentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

            Common.LoadServer();
            db.Server = Common.ServerB;

            StringBuilder sb = new StringBuilder();

            var aem = new ActionEntityMember();
            aem.EntityStagingTableName = "Sandbox_Camera";
            aem.EntityName = "Camera";

            var ca = new CompareAttribute() { AttributeTypeID = 1, DataTypeID = 1, DataTypeInformation = 100 };
            ActionMember am = new ActionMember();
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Code", ValueSource = "CAMERA45" });
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Name", ValueSource = null, ValueDestination = "CAMERA", Attribute = ca });

            aem.LActionMembers.Add(am);
            db.LActionEntityMembers.Add(aem);
            
            db.buildMembers(sb);
            Assert.AreEqual(sb.ToString().ToLower(), Expected.ToString().ToLower());
        }

        [TestMethod]
        public void DataMemberBuilderCaseDifferent2Test()
        {
            StringBuilder Expected = new StringBuilder();
            Expected.Append("DECLARE @BatchTag NVARCHAR(50)\r\n");
            Expected.Append("SELECT @BatchTag = REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')\r\n");
            Expected.Append("PRINT 'BatchTag Details: ' + @BatchTag\r\n");
            Expected.Append("INSERT INTO [stg].[Sandbox_Camera_Leaf]\r\n");
            Expected.Append("(\r\n");
            Expected.Append("	[ImportType]\r\n");
            Expected.Append("	,[ImportStatus_ID]\r\n");
            Expected.Append("	,[BatchTag]\r\n");
            Expected.Append("	,[Code]\r\n");
            Expected.Append("	,[Name]\r\n");
            Expected.Append(")\r\n");
            Expected.Append("SELECT 0 AS 'ImportType', 0 AS 'ImportStatus_ID', @BatchTag AS 'BatchTag', N'CAMERA45' AS 'Code', NULL AS 'Name'\r\n");
            Expected.Append("\r\n");
            Expected.Append("EXEC stg.udp_Sandbox_Camera_Leaf 'VERSION_1', 1, @BatchTag\r\n");
            Expected.Append("GO\r\n");
            Expected.Append("DECLARE @p9 mdm.MemberAttributes\r\n");
            Expected.Append("INSERT INTO @p9 VALUES(N'CAMERA45',N'Name',NULL,NULL,NULL,NULL)\r\n");
            Expected.Append("\r\n");
            Expected.Append("-- get UserID for: BRIGHTSTAR\\oszymczak\r\n");
            Expected.Append("DECLARE @UserID INT\r\n");
            Expected.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = 'BRIGHTSTAR\\oszymczak'\r\n");
            Expected.Append("\r\n");
            Expected.Append("DECLARE @Version_ID INT\r\n");
            Expected.Append("DECLARE @Entity_ID INT\r\n");
            Expected.Append("SELECT @Entity_ID = e.ID, @Version_ID = mv.ID FROM mdm.tblEntity e\r\n");
            Expected.Append("INNER JOIN mdm.tblModel m ON m.ID = e.Model_ID\r\n");
            Expected.Append("INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID\r\n");
            Expected.Append("WHERE e.Name = 'Camera'\r\n");
            Expected.Append("AND m.Name = 'SandBox'\r\n");
            Expected.Append("\r\n");
            Expected.Append("EXEC mdm.udpEntityMembersUpdate @User_ID=@UserID,@Version_ID=@Version_ID,@Entity_ID=@Entity_ID,\r\n");
            Expected.Append("@MemberType_ID=1,@LogFlag=1,\r\n");
            Expected.Append("@DoInheritanceRuleCheck=1,\r\n");
            Expected.Append("@ShouldReturnMembersWithErrorsAsXml=0,\r\n");
            Expected.Append("@IgnorePriorValues=1,\r\n");
            Expected.Append("@MemberAttributes=@p9\r\n");
            Expected.Append("GO\r\n");

            DeploymentBuilder db = new DeploymentBuilder();
            db.CurrentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

            Common.LoadServer();
            db.Server = Common.ServerB;

            StringBuilder sb = new StringBuilder();
            
            var aem = new ActionEntityMember();
            aem.EntityStagingTableName = "Sandbox_Camera";
            aem.EntityName = "Camera";

            var ca = new CompareAttribute() { AttributeTypeID = 1, DataTypeID = 1, DataTypeInformation = 4 };
            ActionMember am = new ActionMember();
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Code", ValueSource = "CAMERA45" });
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Name", ValueSource = null, ValueDestination = "CAMERA", Attribute = ca });

            aem.LActionMembers.Add(am);
            db.LActionEntityMembers.Add(aem);
            
            db.buildMembers(sb);
            Assert.AreEqual(sb.ToString().ToLower(), Expected.ToString().ToLower());
        }

        [TestMethod]
        public void DataMemberBuilderCaseDifferent3Test()
        {
            StringBuilder Expected = new StringBuilder();
            Expected.Append("DECLARE @BatchTag NVARCHAR(50)\r\n");
            Expected.Append("SELECT @BatchTag = REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')\r\n");
            Expected.Append("PRINT 'BatchTag Details: ' + @BatchTag\r\n");
            Expected.Append("INSERT INTO [stg].[Sandbox_Camera_Leaf]\r\n");
            Expected.Append("(\r\n");
            Expected.Append("	[ImportType]\r\n");
            Expected.Append("	,[ImportStatus_ID]\r\n");
            Expected.Append("	,[BatchTag]\r\n");
            Expected.Append("	,[Code]\r\n");
            Expected.Append("	,[OverrideName]\r\n");
            Expected.Append(")\r\n");
            Expected.Append("SELECT 0 AS 'ImportType', 0 AS 'ImportStatus_ID', @BatchTag AS 'BatchTag', N'CAMERA45' AS 'Code', '~NULL~' AS 'OverrideName'\r\n");
            Expected.Append("\r\n");
            Expected.Append("EXEC stg.udp_Sandbox_Camera_Leaf 'VERSION_1', 1, @BatchTag\r\n");
            Expected.Append("GO\r\n");

            DeploymentBuilder db = new DeploymentBuilder();
            db.CurrentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

            Common.LoadServer();
            db.Server = Common.ServerB;

            StringBuilder sb = new StringBuilder();

            var aem = new ActionEntityMember();
            aem.EntityStagingTableName = "Sandbox_Camera";
            aem.EntityName = "Camera";

            var ca = new CompareAttribute() { AttributeTypeID = 1, DataTypeID = 1, DataTypeInformation = 100 };
            ActionMember am = new ActionMember();
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Code", ValueSource = "CAMERA45" });
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "OverrideName", ValueSource = null, ValueDestination = "CAMERA", Attribute = ca });

            aem.LActionMembers.Add(am);
            db.LActionEntityMembers.Add(aem);
            
            db.buildMembers(sb);
            Assert.AreEqual(sb.ToString().ToLower(), Expected.ToString().ToLower());
        }

        [TestMethod]
        public void specialCaseDataMemberBuilderCaseDifferentNullTest()
        {
            DeploymentBuilder db = new DeploymentBuilder();
            db.CurrentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

            Common.LoadServer();
            db.Server = Common.ServerB;
            
            var aem = new ActionEntityMember();
            aem.EntityStagingTableName = "Sandbox_Camera";
            aem.EntityName = "Camera";

            var ca = new CompareAttribute() { AttributeTypeID = 1, DataTypeID = 1, DataTypeInformation = 4 };

            ActionMember am = new ActionMember();
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Name", ValueSource = null, ValueDestination = "CAMERA", Attribute = ca });
            aem.LActionMembers.Add(am);

            StringBuilder sb = new StringBuilder();

            db.buildSpecialMembers(sb, aem);

            Assert.AreEqual(sb.ToString().ToLower(), ExpectedNullCameraResult.ToString().ToLower());
        }

        [TestMethod]
        public void specialCaseDataMemberBuilderCaseDifferentNull2Test()
        {
            DeploymentBuilder db = new DeploymentBuilder();
            db.CurrentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

            Common.LoadServer();
            db.Server = Common.ServerB;

            var aem = new ActionEntityMember();
            aem.EntityStagingTableName = "Sandbox_Camera";
            aem.EntityName = "Camera";

            var ca = new CompareAttribute() { AttributeTypeID = 1, DataTypeID = 1, DataTypeInformation = 4 };

            ActionMember am = new ActionMember();
            am.lMemberValue.Add(new MemberValue() { CodeValue = "CAMERA45", MemberName = "Extra", ValueSource = null, ValueDestination = "CAMERA", Attribute = ca });
            aem.LActionMembers.Add(am);

            StringBuilder sb = new StringBuilder();

            db.buildSpecialMembers(sb, aem);

            Assert.AreEqual(sb.ToString().ToLower(), ExpectedNullCameraExtraResult.ToString().ToLower());
        }

        [TestMethod]
        //Change width of attribute size test
        public void DataAttrributeDifferentSameAttributeTypeIncreaseSize()
        {
            DeploymentBuilder db = new DeploymentBuilder();
            db.CurrentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

            Common.LoadServer();
            db.Server = Common.ServerB;

            var ca = new CompareAttribute() { AttributeTypeID = 1, DataTypeID = 1, DataTypeInformation = 4 };

            ActionAttribute a = new ActionAttribute();
            a.ca = new CompareAttribute();
            a.ca.Entity = new CompareEntity();
            a.caDestination = new CompareAttribute();
            a.caDestination.Entity = new CompareEntity();
            
            a.ca.AttributeTypeID = Constants.ATTRIBUTE_TYPE_FREEFORM;
            a.ca.ChangeTrackingGroup = 1;
            a.ca.DataTypeID = Constants.DATA_TYPES_TEXT;
            a.ca.DataTypeInformation = 40;
            a.ca.DisplayName = "TestAttribute";
            a.ca.DisplayWidth = 40;
            a.ca.DomainEntityMUID = "";
            a.ca.Entity.Name = "TestEntity";
            a.ca.InputMaskID = Constants.InputMask.InputMaskText.NONE;
            a.ca.MemberType = Constants.MEMBER_TYPE_LEAF;
            a.ca.MUID = "943CB73E-3A02-498C-A701-2540653C5CAA";
            a.ca.Name = "TestAttribute";
            a.ca.SortOrder = 1;

            
            a.caDestination.AttributeTypeID = Constants.ATTRIBUTE_TYPE_FREEFORM;
            a.caDestination.DataTypeID = Constants.DATA_TYPES_TEXT;
            a.caDestination.DataTypeInformation = 50;
            a.caDestination.Entity.EntityMUID = "2343CB73E-3A02-498C-A701-2540653C5CBB";
            a.caDestination.Entity.EntityTable = "tbl_3_34_EN";
            a.caDestination.Entity.StagingBase = "SandBox_Test";
            a.caDestination.MUID = "943CB73E-3A02-498C-A701-2540653C5CAA";
            a.caDestination.Name = "TestAttribute";
            a.caDestination.TableColumn = "uda_34_1123";
            


            StringBuilder sb = new StringBuilder();

            db.buildAttributeDifferent(sb, a);

            Assert.AreEqual(sb.ToString().ToLower(), ExpectedNullCameraExtraWidthChangeResult.ToString().ToLower());
        }


    }
}
